﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;

using IT_Tracker.ENTIDADES;

namespace IT_Tracker.DADOS
{
    public class BDOlos
    {
        #region Declarações

        //String de Conexão
        private string conString = @"Data Source=STO-DSK-CC001\SQLEXPRESS;Initial Catalog=OLOSExpurgoDB;Integrated Security=True; User Id=prvuser; Password=prvuser;";
        
        SqlDataAdapter da = new SqlDataAdapter();
       
        List<OlosCall> listaLigacoes = new List<OlosCall>();

        #endregion
        
        //Retorna lista analitica de todas as ligações de uma determinada tabulação
        public List<OlosCall> tabAnalitica(int tab, DateTime datainicial, DateTime datafinal)
        {
            SqlConnection con = new SqlConnection(conString);
            string select = ("SELECT *  FROM [OLOSExpurgoDB].[dbo].[CallData] WHERE DispositionId = " + tab + " AND CallStart >= '" + datainicial + "' AND CallStart <= '" + datafinal + "'");
                        
            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = select;
            try
            {
                con.Open();
                cmd.CommandTimeout = 0;
                SqlDataReader dr = cmd.ExecuteReader();
                
                
                
                //Ler datareader e armazena em uma lista
                while (dr.Read())
                {
                    //campos que não possuem valores nulos
                    OlosCall l = new OlosCall();
                    l.CallId = (int)dr[0];
                    l.CampaignId = (int)dr[1];
                    l.DispositionId = (int)dr[2];
                    l.CallStart = (DateTime)dr[3];
                    l.CallEnd = (DateTime)dr[4];
                                     
                    //Efetuado tratamento para campos que podem ser nulos
                    if (dr[5] != DBNull.Value) { l.AgentStart = Convert.ToDateTime(dr[5]); }
                    if (dr[6] != DBNull.Value) { l.AgentEnd = Convert.ToDateTime(dr[6]); }
                    if (dr[7] != DBNull.Value) { l.WrapStart = (DateTime)dr[7]; }
                    if (dr[8] != DBNull.Value) { l.WrapEnd = (DateTime)dr[8]; }
                    if (dr[9] != DBNull.Value) { l.PhoneNumber = dr[9] as string; }
                    if (dr[10] != DBNull.Value) { l.ANI = dr[10] as string; }
                    if (dr[11] != DBNull.Value) { l.DNIS = dr[11] as string; }
                    if (dr[12] != DBNull.Value) { l.TypePhoneId = (int)dr[12]; }
                    if (dr[13] != DBNull.Value) { l.Route = dr[13] as string; }
                    if (dr[14] != DBNull.Value) { l.Redialed = dr[14] as string; }
                    if (dr[15] != DBNull.Value) { l.AgentId = (int)dr[15]; }
                    if (dr[16] != DBNull.Value) { l.QueueStart = (DateTime)dr[16]; }
                    if (dr[17] != DBNull.Value) { l.QueueEnd = (DateTime)dr[17]; }
                    if (dr[18] != DBNull.Value) { l.MailingName = dr[18] as string; }
                    if (dr[19] != DBNull.Value) { l.ExtraFields = dr[19] as string; }
                    if (dr[20] != DBNull.Value) { l.RecordId = (int)dr[20]; }
                    if (dr[21] != DBNull.Value) { l.PhoneNumberId = (int)dr[21]; }
                    if (dr[22] != DBNull.Value) { l.CustomerId = dr[22] as string; }
                                       

                    listaLigacoes.Add(l);
                }

                return listaLigacoes;
                 
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
 
 

        }

        //Retorna Toda as Tabualações Cadastradas No Sistema
        public DataSet getTabulações()
        {

            SqlConnection con = new SqlConnection(conString);

            try
            {
                con.Open();

                SqlDataAdapter DA = new SqlDataAdapter("SELECT *  FROM [OLOSExpurgoDB].[dbo].[ConfigDisposition]", con);
                DataTable dt = new DataTable();
                DA.Fill(dt);


                DataSet dataset = new DataSet();                
                return dataset;

            }
            catch (Exception ex)
            {
                throw ex;  // retorna mensagem de erro
            }
            finally
            {
                con.Close();
            }


        }
        /*
        public DataSet getTabTotalDia()
        {
            SqlConnection con = new SqlConnection(conString);

            List<ConfigDisp> lstOlosConfig = new List<ConfigDisp>();

            try
            {
                con.Open();

               

            }
            catch (Exception ex)
            {
                
                throw ex;
            }

            finally            
            {
                con.Close();
            }
        }
        */
    }
}
